﻿/*
*┌────────────────────────────────────────────────┐
*│　描    述：DataSetUtility                                                   
*│　作    者： (Jxw)                                             
*│　版    本：1.0     
*│　功能描述：                                          
*│　创建时间：2021/4/21 14:38:10                        
*└────────────────────────────────────────────────┘
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utility;

namespace Data
{
    public class DataSetUtility
    {
        public static DataSet GetDataSetFromTold(StringBuilder sb)
        {
            return SqlHelper.ExecuteDataset(AppConfig.ConnString, CommandType.Text, sb.ToString(), null);
        }
        public static DataSet GetDataSetFromTold(StringBuilder sb, SqlParameter sp)
        {
            return SqlHelper.ExecuteDataset(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp);
        }
        public static DataSet GetDataSetFromTold(StringBuilder sb, SqlParameter[] sps)
        {
            return SqlHelper.ExecuteDataset(AppConfig.ConnString, CommandType.Text, sb.ToString(), sps);
        }
        public static int GetNonQueryFromTold(StringBuilder sb)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.ConnString, CommandType.Text, sb.ToString(), null);
        }
        public static int GetNonQueryFromTold(StringBuilder sb, SqlParameter sp)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp);
        }
        public static int GetNonQueryFromTold(StringBuilder sb, SqlParameter[] sps)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.ConnString, CommandType.Text, sb.ToString(), sps);
        }


        #region V7库
        public static DataSet GetDataSetFromMain(StringBuilder sb)
        {
            return SqlHelper.ExecuteDataset(AppConfig.MainConnString, CommandType.Text, sb.ToString(), null);
        }
        public static DataSet GetDataSetFromMain(StringBuilder sb, SqlParameter sp)
        {
            return SqlHelper.ExecuteDataset(AppConfig.MainConnString, CommandType.Text, sb.ToString(), sp);
        }
        public static DataSet GetDataSetFromMain(StringBuilder sb, SqlParameter[] sps)
        {
            return SqlHelper.ExecuteDataset(AppConfig.MainConnString, CommandType.Text, sb.ToString(), sps);
        }
        public static int GetNonQueryFromMain(StringBuilder sb)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.MainConnString, CommandType.Text, sb.ToString(), null);
        }
        public static int GetNonQueryFromMain(StringBuilder sb, SqlParameter sp)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.MainConnString, CommandType.Text, sb.ToString(), sp);
        }
        public static int GetNonQueryFromMain(StringBuilder sb, SqlParameter[] sps)
        {
            return SqlHelper.ExecuteNonQuery(AppConfig.MainConnString, CommandType.Text, sb.ToString(), sps);
        }
        #endregion






        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static int GetCountFromTold(StringBuilder sb)
        {
            int count = (int)SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), null);
            return count;
        }
        /// <summary>
        /// 根据单个条件获取总数
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static int GetCountFromTold(StringBuilder sb, SqlParameter sp)
        {
            int count = (int)SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp);
            return count;
        }
        /// <summary>
        /// 根据多个条件获取总数
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static int GetCountFromTold(StringBuilder sb, SqlParameter[] sps)
        {
            int count = (int)SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), sps);
            return count;
        }

        #region 批量插入
        public static void BulkInsert(DataTable dt, string destinationTableName)
        {

            Stopwatch sw = new Stopwatch();
            //DataTable dt = GetTableSchema();
            string connectionString = AppConfig.ConnString;//数据库连接
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = destinationTableName;
                int totalRow = dt.Rows.Count;
                bulkCopy.BatchSize = totalRow;
                conn.Open();
                sw.Start();

                //for (int i = 0; i < totalRow; i++)
                //{
                //    DataRow dr = dt.NewRow();
                //    dr[0] = Guid.NewGuid();
                //    dr[1] = string.Format("商品", i);
                //    dr[2] = (decimal)i;
                //    dt.Rows.Add(dr);
                //}

                if (dt != null && dt.Rows.Count != 0)
                {
                    try
                    {
                        bulkCopy.WriteToServer(dt);
                        sw.Stop();
                    }
                    catch (Exception ex)
                    {

                        throw;
                    }

                }
                //Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(sw.ElapsedMilliseconds)));
                Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds));
            }
        }


        /// <summary>
        /// 将datatable数据bulkcopy进SQL table
        /// </summary>
        /// <param name="dt">要插入的数据</param>
        /// <param name="connectionString">连接数据库</param>
        /// <param name="tablename">数据库的表名</param>
        /// <param name="bulkCopyTimeout">设置超时的时间</param>
        public static void SqlBulkCopy(DataTable dt, string connectionString, string tablename, int? bulkCopyTimeout = null)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    if (sqlConn.State != ConnectionState.Open)
                        sqlConn.Open();
                    /*查询数据 tablename对应的字段*/
                    string[] restrictions = new string[4];
                    restrictions[1] = "dbo";
                    restrictions[2] = tablename;
                    /*获取table的列*/
                    DataTable tableinfo = sqlConn.GetSchema("Columns", restrictions);
                    if (tableinfo.Rows.Count > 0)
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
                        {
                            if (bulkCopyTimeout != null)
                            {
                                bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                            }
                            bulkCopy.DestinationTableName = tablename;
                            bulkCopy.BatchSize = 1000;//每1000条提交一次
                            if (dt.Columns.Count != tableinfo.Rows.Count)
                            {

                            }
                            foreach (DataColumn dc in dt.Columns)
                            {
                                foreach (DataRow dr in tableinfo.Rows)
                                {
                                    //动态mapping字段，大小写可以忽略
                                    if (dc.ColumnName.ToLower() == dr["COLUMN_NAME"].ToString().ToLower())
                                    {
                                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dr["COLUMN_NAME"].ToString());
                                        break;//只要匹配到就跳出该次循环，进行下一次
                                    }
                                }
                            }
                            bulkCopy.WriteToServer(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogUtility.Error("Utility/DataSetUtility/SqlBulkCopy", ex.ToString());
                throw;
            }
        }


        /// <summary>
        /// 将datatable数据bulkcopy进SQL table
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tablename">数据库表名</param>
        /// <param name="connectionString">连接数据库</param>
        /// <param name="bulkCopyTimeout">设置超时的时间</param>
        /// <param name="columnmapping"></param>
        public static void SqlBulkCopy(DataTable dt, string tablename, string connectionString, SqlBulkCopyColumnMapping[] columnmapping, int? bulkCopyTimeout = null)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    if (sqlConn.State != ConnectionState.Open)
                        sqlConn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
                    {
                        if (bulkCopyTimeout != null)
                        {
                            bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                        }
                        bulkCopy.DestinationTableName = tablename;
                        bulkCopy.BatchSize = 1000;//每1000条提交一次
                                                  //循环mapping
                        foreach (SqlBulkCopyColumnMapping map in columnmapping)
                        {
                            bulkCopy.ColumnMappings.Add(map);
                        }
                        bulkCopy.WriteToServer(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                LogUtility.Error("Error:Connercthelper SqlBulkCopy", ex.ToString());
                throw;
            }
        }
        #endregion


        #region 分页查询 返回DataTable

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename">表名</param>
        /// <param name="ziduan">字段</param>
        /// <param name="strWhere">条件</param>
        /// <param name="orderby">排序条件</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页长</param>
        /// <returns></returns>
        public static DataTable GetListDTPageCom(string tablename, string ziduan, string strWhere, string orderby, int pageIndex, int pageSize)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT top " + pageSize + " " + ziduan + " FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()) && !orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by T." + orderby);
            }
            else if (!string.IsNullOrEmpty(orderby.Trim()) && orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by " + orderby);// CHARINDEX('河北废金属回收',T.gq_title) asc
            }
            else
            {
                // strSql.Append("order by T.gq_id desc");
            }
            strSql.Append(")AS Row, T.*  from " + tablename + " T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE 1=1  " + strWhere);
            }
            int page = (pageIndex - 1) * pageSize;
            strSql.Append(" ) TT  WHERE TT.Row >" + page + "");
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(AppConfig.ConnString, CommandType.Text, strSql.ToString(), null);

            if (ds != null)
            {

                return ds.Tables[0];

            }
            else
            {
                return null;
            }
        }

        #endregion


        #region 获取查询总条数
        /// <summary>
        /// 获取查询总条数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="strWhere">查询条件</param>

        /// <returns></returns>
        public static int GetListRecordCount(string tablename, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM " + tablename + " ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            object obj = null;

            obj = SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, strSql.ToString(), null);


            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }


        }

        #endregion



        #region 获取最大值
        /// <summary>
        /// 获取最大ID值
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static int GetMaxIDFromTold(StringBuilder sb, SqlParameter sp)
        {
            //int maxId = (int)SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp);
            string result = SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp).ToString();
            int maxId = 0;
            if (!string.IsNullOrEmpty(result))
            {
                maxId = Convert.ToInt32(result);
            }
            //int maxId = result == "" ? 0 : Convert.ToInt32(result);
            return maxId;
        }


        public static int GetMaxIDFromTold(StringBuilder sb)
        {
            //int maxId = (int)SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), sp);
            string result = SqlHelper.ExecuteScalar(AppConfig.ConnString, CommandType.Text, sb.ToString(), null).ToString();
            int maxId = 0;
            if (!string.IsNullOrEmpty(result))
            {
                maxId = Convert.ToInt32(result); ;
            }
            //int maxId = result == "" ? 0 : Convert.ToInt32(result);
            return maxId;
        }
        #endregion




    }
}
